package Persistencia;

import Clases.Importacion;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class conexionBD {

    private static String UserName = "postgres";
    private static String Pass = "postgres";
    private static String BD = "jdbc:postgresql://10.71.0.5:5432/siec";

    public static List<Importacion> obtenerDeBD(String consulta, String nombre) {
        List<Importacion> salida = new ArrayList<Importacion>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            ResultSet rs = s.executeQuery(consulta);

            if (nombre.equalsIgnoreCase("noCargados")) {
                while (rs.next()) {
                    Importacion imp = new Importacion();
                    imp.setInstitucion(rs.getString("institucion"));
                    imp.setAnio(rs.getInt(""));
                    imp.setMes(rs.getInt(""));
                    imp.setTipoFuente(rs.getString("TipoFuente"));
                    imp.setFechaCarga(rs.getString("").toString());
                    salida.add(imp);
                }
            } else {
                while (rs.next()) {
                    Importacion imp = new Importacion();
                    imp.setInstitucion(rs.getString("institucion"));
                    imp.setAnio(rs.getInt("anio"));
                    imp.setMes(rs.getInt("mes"));
                    imp.setTipoFuente(rs.getString("TipoFuente"));
                    imp.setFechaCarga(rs.getString("FechaHora").toString());
                    salida.add(imp);
                }
            }


            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return salida;
    }

    public static List<Importacion> noCargadas(String filtro, String anioMes) {
        List<Importacion> salida = new ArrayList<Importacion>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "Select *, 0 as anio, 0 as mes, '' as FechaHora from "
                    + "(Select Distinct(Institucion) as Institucion, 'SINADI_Asistencial' as Tipofuente "
                    + "from etl_importacion "
                    + "Where institucion not in "
                    + "(Select institucion from etl_importacion "
                    + "where tipofuente = 'SINADI_Asistencial' and " + anioMes + ")"
                    + "union all "
                    + "Select Distinct(Institucion) as Institucion, 'SINADI_Financiero' as Tipofuente from etl_importacion "
                    + "Where institucion not in "
                    + "(Select institucion from etl_importacion  "
                    + "where tipofuente = 'SINADI_Financiero' and " + anioMes + ")) as Tabla ";

            if (filtro != null) {
                query += filtro;
            }
            query += " Order by Institucion";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                Importacion imp = new Importacion();
                imp.setInstitucion(rs.getString("institucion"));
                imp.setAnio(rs.getInt("anio"));
                imp.setMes(rs.getInt("mes"));
                imp.setTipoFuente(rs.getString("TipoFuente"));
                imp.setFechaCarga(rs.getString("FechaHora").toString());
                salida.add(imp);
            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return salida;
    }

    public static List<Importacion> cantidadFaltante(String filtro) {
        List<Importacion> salida = new ArrayList<Importacion>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "select Count(Distinct(mes)) as Cant, tipofuente, Institucion, anio, '' as FechaHora "
                    + "from etl_importacion " + filtro
                    + "group by  tipofuente, Anio,  Institucion "
                    + "order by  institucion, tipofuente";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                Importacion imp = new Importacion();
                imp.setInstitucion(rs.getString("institucion"));
                imp.setAnio(rs.getInt("anio"));
                imp.setMes(rs.getInt("Cant"));
                imp.setTipoFuente(rs.getString("TipoFuente"));
                imp.setFechaCarga(rs.getString("FechaHora").toString());
                salida.add(imp);
            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return salida;
    }

    public static List<Importacion> ultimaCarga(String filtro) {
        List<Importacion> salida = new ArrayList<Importacion>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "Select max(a.anio) as Anio, "
                    + "(Select max(Mes) From etl_importacion "
                    + "where anio = max(a.anio) and institucion = a.institucion and TipoFuente = a.TipoFuente) as Mes, "
                    + "a.institucion, a.TipoFuente from etl_importacion a " + filtro
                    + "Group by institucion, TipoFuente "
                    + "order by anio, institucion, TipoFuente asc";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                Importacion imp = new Importacion();
                imp.setInstitucion(rs.getString("institucion"));
                imp.setAnio(rs.getInt("anio"));
                imp.setMes(rs.getInt("mes"));
                imp.setTipoFuente(rs.getString("TipoFuente"));
                imp.setFechaCarga("");
                salida.add(imp);
            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return salida;
    }

    public static List<Importacion> fechaUltimaCarga(String filtro) {
        List<Importacion> salida = new ArrayList<Importacion>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "Select max(FechaHora) as FechaCarga, "
                    + "institucion, TipoFuente from etl_importacion " + filtro
                    + "Group by institucion, TipoFuente "
                    + "order by institucion, TipoFuente asc";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                Importacion imp = new Importacion();
                imp.setInstitucion(rs.getString("institucion"));
                imp.setAnio(0);
                imp.setMes(0);
                imp.setTipoFuente(rs.getString("TipoFuente"));
                imp.setFechaCarga(rs.getString("FechaCarga"));
                salida.add(imp);
            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return salida;
    }

    public static List<String> obtenerTipos() {
        List<String> salida = new ArrayList<String>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "Select Distinct(tipofuente) as Tipo From etl_importacion order by tipofuente asc";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                salida.add(rs.getString("Tipo"));

            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
            salida.add("ERROR " + e);
        }
        return salida;
    }

    public static List<String> obtenerAnios() {
        List<String> salida = new ArrayList<String>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "Select Distinct(ANIO) as anio From etl_importacion order by anio asc";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                salida.add(rs.getString("anio"));

            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
            salida.add("ERROR " + e);
        }
        return salida;
    }

    public static List<String> obtenerMeses() {
        List<String> salida = new ArrayList<String>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "Select Distinct(MES) as Mes From etl_importacion order by mes asc";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                salida.add(rs.getString("Mes"));

            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
            salida.add("ERROR " + e);
        }
        return salida;
    }

    public static List<String> obtenerInstituciones() {
        List<String> salida = new ArrayList<String>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "Select Distinct(Institucion) as Institucion From etl_importacion order by Institucion asc";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                salida.add(rs.getString("Institucion"));

            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
            salida.add("ERROR " + e);
        }
        return salida;
    }

    public static List<String> obtenerNoCargados(String anio, String mes, String tipo) {
        List<String> salida = new ArrayList<String>();

        try {
            Class.forName("org.postgresql.Driver");

            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "";
            String where = "";
            if (anio != null) {
                where += "and Anio = '" + anio + "' ";
                if (mes != null) {
                    where += "and Mes = '" + mes + "' ";
                }
            }

            query = "Select Distinct(Institucion) as Institucion  from etl_importacion "
                    + "Where institucion not in "
                    + "(Select institucion from etl_importacion "
                    + "where tipofuente = '" + tipo + "' " + where + ") ";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                salida.add(rs.getString("Institucion"));

            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();
            salida.add("ERROR " + e);
        }
        return salida;
    }

    /*
     * public static String getCarga(String Descripcion) { List lista =
     * obtenerDeBD("etl_importacion", Descripcion); if (lista.size() > 0) {
     * return (String) lista.get(0); } return null; }
     *
     * public static String getParamOS(String Descripcion) { List lista =
     * obtenerDeBD("OS", Descripcion); if (lista.size() > 0) { return (String)
     * lista.get(0); } return null; }
     *
     * public static String[] obtenerTodos(String tipo) { List<Importacion>
     * lista = obtenerDeBD("etl_importacion", null);
     *
     * String[] array = new String[lista.size()]; /* for (int i = 0; i <
     * lista.size(); i++) { array[i] = lista.get(i); }
     *
     * return array; }
     */
    public static Long getNumOrdServ() {
        Long salida = new Long(0);

        try {

            Class.forName("org.postgresql.Driver");


            Connection conexion = (Connection) DriverManager.getConnection(
                    BD, UserName, Pass);

            Statement s = (Statement) conexion.createStatement();

            String query = "select idOS()";

            ResultSet rs = s.executeQuery(query);

            while (rs.next()) {
                salida = rs.getLong(1);
            }

            conexion.close();
        } catch (Exception e) {
            e.printStackTrace();

        }
        return salida;
    }
}
